package com.geekaca.jdbc.homework;

import java.sql.*;
import java.util.Scanner;

public class Work1 {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {

        Scanner scanner = new Scanner(System.in);
        // 1 向表中插入1条数据
        Connection connection = getConnection();
        Statement statement = connection.createStatement();
        String sql1 = "insert into dongtu_emp values( null,'蜘蛛精0','1650-09-06',5000,163,3)";
//        int rows = statement.executeUpdate(sql1);

        // 2 向表中插入100条数据
//        insert100(statement);

        // 3 查询出表的所有数据展示出来
//        showResult(statement);

        // 4 动态发送sql
        insertData(connection, statement, scanner);

        // 进阶, 查询员工的信和所属部门的名字
//        queryEmpDp(statement);

        // 模糊查询, 输入"猪" 查出 猪八戒,猪九戒
//        blurQuery(statement,scanner);

        statement.close();
        connection.close();

    }

    private static void blurQuery(Statement statement, Scanner scanner) throws SQLException {
        System.out.println("请输入要查询的名字:");
        String username = scanner.next();
        username = "'%" + username + "%'";
        String sql = "select id, name, hire_date, salary, height, dp_name from dongtu_emp, dongtu_dp where dongtu_emp" +
                ".dp_id = dongtu_dp.dp_id and name like " + username + ";";

        System.out.println(sql);


        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            Date hire_date = resultSet.getDate("hire_date");
            int salary = resultSet.getInt("salary");
            double height = resultSet.getDouble("height");
            String dp_name = resultSet.getString("dp_name");
            System.out.println("id: " + id + ", name: " + name + ", hire_date " + hire_date + ", salary " + salary +
                    ", height: " + height + ", dp_name: " + dp_name);
        }

    }

    private static void queryEmpDp(Statement statement) throws SQLException {
        String sql = "select id, name, hire_date, salary, height, dp_name from dongtu_emp, dongtu_dp where dongtu_emp" +
                ".dp_id = dongtu_dp.dp_id;";

        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            Date hire_date = resultSet.getDate("hire_date");
            int salary = resultSet.getInt("salary");
            double height = resultSet.getDouble("height");
            String dp_name = resultSet.getString("dp_name");
            System.out.println("id: " + id + ", name: " + name + ", hire_date " + hire_date + ", salary " + salary +
                    ", height: " + height + ", dp_name: " + dp_name);
        }


    }

    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        // 加载驱动
        Class.forName("com.mysql.jdbc.Driver");

        Connection connection = null;
        try {
            String url = "jdbc:mysql://127.0.0.1:3306/dbemp?useSSL=false&characterEncoding=utf8";
            String username = "root";
            String password = "123456";

            connection = DriverManager.getConnection(url, username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return connection;


    }

    public static void showResult(Statement statement) throws SQLException {
        ResultSet resultSet = statement.executeQuery("select * from dongtu_emp");
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            Date hire_date = resultSet.getDate("hire_date");
            int salary = resultSet.getInt("salary");
            double height = resultSet.getDouble("height");
            System.out.println("id: " + id + " name: " + name + " hire_date " + hire_date + " salary " + salary + " height: " + height);
        }
    }


    public static void insert100(Statement statement) throws SQLException {
        String sqlbegin = "insert into dongtu_emp values( null,'蜘蛛精";
        String sqlend = "' ,'1650-09-06',5000,163,3) ";
        for (int i = 1; i < 101; i++) {
            String sqlAll = sqlbegin + i + sqlend;
            System.out.println(sqlAll);
            statement.executeUpdate(sqlAll);
        }
    }

    public static void insertData(Connection connection, Statement statement, Scanner scanner) throws SQLException {
        System.out.println("输入用户名: ");
        String username = scanner.next();
        System.out.println("输入工资: ");
        int saraly = scanner.nextInt();
        System.out.println("输入入职日期");
        String date = scanner.next();

        System.out.println("输入身高: ");
        double height = scanner.nextDouble();


//        String sql =
//                "insert into dongtu_emp values( null,'" + username + "','" + date + "'," + saraly + "," + height + "," +
//                        "3)";


//        System.out.println(sql);
//
//        statement.executeUpdate(sql);

        // 预编译方式
        String sql = "insert into dongtu_emp values(null,?, ? , ? , ? ,3)";

        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setString(1, username);
        pstmt.setString(2, date);
        pstmt.setDouble(3, saraly);
        pstmt.setDouble(4, height);
        System.out.println(sql);
        pstmt.executeUpdate();


    }
}
